import pandas as pd
import matplotlib.pyplot as plt
import numpy as np

a = pd.read_excel('1.xlsx', dtype={'code': 'str'})
a.set_index('code', inplace=True)
print(a.loc['002222'])
print(a.groupby('area').size().sort_values(ascending=False))
c = a.timeToMarket.astype('str').str[:4]
c = a.groupby(c).size()
plt.rcParams['font.sans-serif'] = ['SimHei']
#plt.rcParams['axes.unicode_minus'] = False
c[c.index!='0'].plot(fontsize=14, title='年IPO数量')
plt.show()
print(a[a.pe > 0].pe.mean())
a['tvalue'] = 4 * a.esp * a.pe * a.totals
print(np.sum(a.pe * a.tvalue) / a.tvalue.sum())